# -*- coding: utf-8 -*-
"""
Created on Mon Jul 15 09:00:47 2019

@author: R720-15
"""

from pandas import *
from matplotlib.pyplot import *
import sqlite3
import sqlalchemy
engine = sqlalchemy.create_engine('sqlite:///bra111.sqlite')
rcParams['font.sans-serif'] = ['SimHei']
options.display.float_format = '{:,.2f}%'.format

sales = read_sql('select source,size1,size2 from t_sales',engine)
size1size2Count = sales.groupby(['size1','size2'])['size1'].count()
print(size1size2Count)
size1size2Total = size1size2Count.sum()
print(size1size2Total)
size1size2 = size1size2Count.to_frame(name='销量')
n = 500
# 过滤出销量小等于500的组，并统计这些组的总销量，将统计结果放到DataFrame中
others = DataFrame([size1size2[size1size2['销量'] <= 
     n].sum()],index=MultiIndex(levels=[[''],['其他']],labels=[[0],[0]]))


# 将“其他”销量放到记录集的最后
size1size2 = size1size2[size1size2['销量']>n].append(others)
print(size1size2)

size1size2 = size1size2.sort_values(['销量'],ascending=[0])
size1size2.insert(0,'比例',100 * size1size2Count / size1size2Total)
print(size1size2)
labels = size1size2.index.tolist()
newLabels = []
# 生成饼图外侧显示的每一部分的表示（如75B、80A等）
for label in labels:
    newLabels.append(label[1] + label[0])
pie(size1size2['销量'],labels=newLabels,autopct='%.2f%%')
legend()
axis('equal')
title('罩杯+上胸围销售比例')
show()

engine = sqlalchemy.create_engine('sqlite:///bra111.sqlite')
rcParams['font.sans-serif'] = ['SimHei']
options.display.float_format = '{:,.2f}%'.format
sales = read_sql('select source,color1 from t_sales',engine)
# 按color1分组，并统计每组的数量
color1Count = sales.groupby('color1')['color1'].count()
# 统计总销量
color1Total = color1Count.sum()
print(color1Total)
color1 = color1Count.to_frame(name='销量')
print(color1)
color1.insert(0,'比例', 100 * color1Count / color1Total)
color1.index.names=['颜色']
color1 = color1.sort_values(['销量'], ascending=[0])
print(color1)
n = 100
# 销量小于等于1200都属于“其他”分组
others = DataFrame([color1[color1['销量'] <= n].sum()],index=MultiIndex(levels=[['其他']],labels=[[0]]))
# 将others添加到原来的记录集中
color1 = color1[color1['销量']>n].append(others)
print(color1)
# 将索引转换为在饼图周围显示的标签
labels = color1.index.tolist()
pie(color1['销量'],labels=labels,autopct='%.2f%%')
legend()
axis('equal')
title('按胸罩颜色统计的比例')
show()